﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Collections;
using generic_class;

namespace my_database
{
    public class MyDatabase
    {
        private MySqlConnection conn;
        private Hashtable htNode;
        private int scdid;
        public bool init_connection(int scdid)
        {
            try
            {
                this.scdid = scdid;
                htNode = new Hashtable();
                conn = new MySqlConnection("Database=smartdb;Data Source=localhost;User Id=root;Password=zwxx");
                conn.Open();
                MySqlCommand command = new MySqlCommand("set names gb2312", conn);
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("An error occurred: '{0}'", e);
                conn.Close();
                return false;
            }

            return true;
        }

        private String GetString(MySqlDataReader reader, int i)
        {
            if (reader.IsDBNull(i))
                return null;
            else
                return reader.GetString(i);
        }

        private int? GetInt16(MySqlDataReader reader, int i)
        {
            if (reader.IsDBNull(i))
                return null;
            else
                return reader.GetInt16(i);
        }

        private double? GetDouble(MySqlDataReader reader, int i)
        {
            if (reader.IsDBNull(i))
                return null;
            else
                return reader.GetDouble(i);
        }

        private String getArrayString(int[] array)
        {
            StringBuilder result = new StringBuilder();
            for (int i = 0; i < array.Length; i++)
            {
                result.Append(array[i].ToString());
                if (i < array.Length - 1)
                    result.Append(',');
            }
            return result.ToString();

        }


        //获取主菜单列表元素,group_table
        public void get_main_pallete_list(ArrayList array)
        {
            string sql = "select * from group_table where scdid=" + scdid + " order by parentId,idgroup_table";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                group_item item = new group_item();

                item.idgroup = (int)reader["idgroup_table"];
                item.parentId = (int)reader["parentId"];
                item.name = (String)reader["name"];

                array.Add(item);
            }

            reader.Close();

            string sql2 = "select * from dwg_table where scdid=" + scdid + " order by parentId,iddwg_table";

            MySqlCommand command2 = new MySqlCommand(sql2, conn);
            MySqlDataReader reader2 = command2.ExecuteReader();

            while (reader2.Read())
            {
                group_item item = new group_item();

                item.idgroup = (int)reader["idgroup_table"];
                item.parentId = (int)reader["parentId"];
                item.name = (String)reader["name"];

                array.Add(item);
            }
            reader2.Close();

        }

        //根据parentid找到group信息
        //返回group_item类型group信息数组
        public ArrayList get_group_by_parent(int scdid, int idparent)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select  *  from group_table where scdid=" + scdid + " and parentId =" + idparent + " order by idgroup_table";
            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                group_item item = new group_item();
                item.idgroup = reader.GetInt16(0);
                item.parentId = GetInt16(reader, 1);
                item.name = GetString(reader, 2);
                item.scdid = GetInt16(reader, 3);

                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }

        //根据设备id找到设备信息
        //ied_table,根据设备id或者id数组,查找设备信息,
        //返回ied_item类型设备信息或者信息数组
        public ArrayList get_ied(int[] idied_table)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select  *  from ied_table where scdid=" + scdid + " and idied_table in (" + getArrayString(idied_table) + ") order by idied_table";
            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                ied_item item = new ied_item();

                item.idied = reader.GetInt16(0);
                item.name = GetString(reader, 1);
                item.len = GetDouble(reader, 3);
                item.scdid = GetInt16(reader, 4);
                item.type = GetString(reader, 5);
                item.manufacturer = GetString(reader, 6);
                item.ieddesc = GetString(reader, 7);
                item.configVersion = GetString(reader, 8);
                item.inUse = reader.GetInt16(9);
                item.deviceType = GetInt16(reader, 10);
                item.tangid = GetInt16(reader, 11);
                item.vLevel = GetInt16(reader, 12);
                item.bay = GetString(reader, 13);
                item.iedType = GetString(reader, 14);
                item.deviceNo = GetString(reader, 15);
                item.TangCode = GetString(reader, 16);
                item.TangPos = GetString(reader, 17);

                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }


        //根据设备名称找到对应的屏柜
        //ied_table,tang_table,根据设备id或者id数组,查找屏柜信息,fiber 数量,
        //返回tang_item类型屏柜信息或者信息数组,包括屏柜名称和每个屏柜的fiber列表
        //举例：ArrayList alIed = get_tang_according_to_device(6);
        public ArrayList get_tang_from_device(int[] idied_table)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select  t.*  from ied_table i,tang_table t where i.scdid=" + scdid + " and i.tangid=t.idtang  and i.scdid=t.scdid"
                + " and i.idied_table in (" + getArrayString(idied_table) + ") order by idied_table";
            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                tang_item item = new tang_item();

                item.idtang = reader.GetInt16(0);
                item.objectDesc = GetString(reader, 1);
                item.objectType = GetString(reader, 2);
                item.objectSize = GetString(reader, 3);
                item.manufacture = GetString(reader, 4);
                item.scdid = GetInt16(reader, 5);
                item.gpRow = GetString(reader, 6);
                item.rowPortNum = GetString(reader, 7);
                //item.roomid = GetInt16(reader, 8);

                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }

        //根据起始屏柜找到对应的光缆
        //tangid,根据id或者id数组,查找光缆信息,fiber 数量,
        //返回cable_item类型光缆信息数组,
        //举例：ArrayList al = get_cable_from_start_tang(16);
        public ArrayList get_cable_from_start_tang(int[] tangid)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select * from cable_table where scdid=" + scdid + " and tang1 in (" + getArrayString(tangid) + ") order by tang1";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                cable_item item = new cable_item();

                item.idcable = reader.GetInt16(0);
                item.name = GetString(reader, 1);
                item.cableType = GetString(reader, 2);
                item.scdid = scdid;
                item.start_tangid = GetInt16(reader, 4);
                item.end_tangid = GetInt16(reader, 5);
                item.fiberNum = GetString(reader, 6);
                item.linkType = GetString(reader, 7);
                item.locked = GetInt16(reader, 8);
                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }

        //根据终止屏柜找到对应的光缆
        //tangid,根据id或者id数组,查找光缆信息,fiber 数量,
        //返回cable_item类型光缆信息数组,
        //举例：ArrayList al = get_cable_from_end_tang(16);
        public ArrayList get_cable_from_end_tang(int[] tangid)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select * from cable_table where scdid=" + scdid + " and tang2 in (" + getArrayString(tangid) + ") order by tang2";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                cable_item item = new cable_item();

                item.idcable = reader.GetInt16(0);
                item.name = GetString(reader, 1);
                item.cableType = GetString(reader, 2);
                item.scdid = scdid;
                item.start_tangid = GetInt16(reader, 4);
                item.end_tangid = GetInt16(reader, 5);
                item.fiberNum = GetString(reader, 6);
                item.linkType = GetString(reader, 7);
                item.locked = GetInt16(reader, 8);
                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }

        //根据双向屏柜找到对应的光缆
        //tangid,根据id或者id数组,查找光缆信息,fiber 数量,
        //返回cable_item类型光缆信息数组,
        //举例：ArrayList al = get_cable_from_tang(16);
        public ArrayList get_cable_from_tang(int[] tangid)
        {
            ArrayList alItems = new ArrayList();
            String strTangid = getArrayString(tangid);
            string sql = "select * from cable_table where scdid=" + scdid
                + " and tang1 in (" + strTangid + ") or tang2 in (" + strTangid + ") order by idcable_table";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                cable_item item = new cable_item();

                item.idcable = reader.GetInt16(0);
                item.name = GetString(reader, 1);
                item.cableType = GetString(reader, 2);
                item.scdid = scdid;
                item.start_tangid = GetInt16(reader, 4);
                item.end_tangid = GetInt16(reader, 5);
                item.fiberNum = GetString(reader, 6);
                item.linkType = GetString(reader, 7);
                item.locked = GetInt16(reader, 8);
                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }

        //根据光缆找到对应的纤芯
        //tangid,根据id或者id数组,查找光缆信息,fiber 数量,
        //返回line_item类型光缆信息数组,
        //举例：ArrayList al = get_line_from_cable(16);
        public ArrayList get_line_from_cable(int[] cableid)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select * from line_table where scdid=" + scdid + " and parentid in (" + getArrayString(cableid) + ") order by parentid";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                line_item item = new line_item();

                item.idline = reader.GetInt16(0);
                item.port1 = GetInt16(reader, 1);
                item.port2 = GetInt16(reader, 2);
                item.lineDesc = GetString(reader, 3);
                item.manufacture = GetString(reader, 4);
                item.lineType = GetString(reader, 5);
                item.length = GetDouble(reader, 6);
                item.scdid = scdid;
                item.crossShelf1 = GetString(reader, 8);
                item.shelfPortId1 = GetInt16(reader, 9);
                item.shelfPortIndex = GetInt16(reader, 10);
                item.parentid = GetInt16(reader, 11);
                item.crossShelf2 = GetString(reader, 12);
                item.shelfPortId2 = GetInt16(reader, 13);
                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }

        //cableid,根据id或者id数组,查找光配信息,
        //返回端口信息数组,
        //举例：ArrayList al = get_gp_from_cable(16);
        public ArrayList get_gp_from_cable(int[] cableid)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select * from gp_table where scdid=" + scdid + " and cableid in (" + getArrayString(cableid) + ") order by cableid";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                gp_item item = new gp_item();

                item.idgp = reader.GetInt16(0);
                item.cableid = GetInt16(reader, 1);
                item.tang = GetInt16(reader, 2);
                item.idline = GetInt16(reader, 3);
                item.locked = GetInt16(reader, 4);
                item.row = GetInt16(reader, 5);
                item.col = GetInt16(reader, 6);
                item.scdid = GetInt16(reader, 7);
                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }



        //portid,根据id或者id数组,查找端口信息,
        //返回端口信息数组,
        //举例：ArrayList al = get_port_from_parent(16);
        public ArrayList get_port(int[] portid)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select * from port_table where scdid=" + scdid + " and idport_table in (" + getArrayString(portid) + ") order by idport_table";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                port_item item = new port_item();

                item.idport = reader.GetInt16(0);
                item.parentid = GetInt16(reader, 1);
                item.portType = GetInt16(reader, 2);
                item.portDesc = GetString(reader, 3);
                item.vlanType = GetInt16(reader, 4);
                item.vlan = GetString(reader, 5);
                item.parentType = GetInt16(reader, 6);
                item.fiberType = GetString(reader, 7);
                item.sendType = GetInt16(reader, 8);
                item.scdid = GetInt16(reader, 9);
                item.groupName = GetString(reader, 10);
                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }

        //folderid,根据id或者id数组,查找插件信息,
        //返回插件信息数组,
        //举例：ArrayList al = get_folder(16);
        public ArrayList get_folder(int[] folderid)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select * from folder_table where scdid=" + scdid + " and idfolder_table in (" + getArrayString(folderid) + ") order by idfolder_table";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                folder_item item = new folder_item();

                item.idfolder = reader.GetInt16(0);
                item.name = GetString(reader, 1);
                item.objectDesc = GetString(reader, 2);
                item.deviceId = GetInt16(reader, 3);
                item.scdid = GetInt16(reader, 4);
                item.manufacturer = GetString(reader, 5);
                item.folderType = GetString(reader, 6);
                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }

        //portid,根据id或者id数组,查找连接信息,
        //返回inforline_item类型连接信息数组,
        //举例：ArrayList al = get_folder(16);
        public ArrayList get_inforline_from_port(int[] portId)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select * from inforline_table where scdid=" + scdid + " and portid in (" + getArrayString(portId) + ") order by idinforline_table";

            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                inforline_item item = new inforline_item();

                item.idinforline = reader.GetInt16(0);
                item.name = GetString(reader, 1);
                item.portid = GetInt16(reader, 2);
                item.scdid = GetInt16(reader, 3);
                item.inforDesc = GetString(reader, 4);
                item.inforIndex = GetInt16(reader, 5);
                item.linkType = GetString(reader, 6);
                item.gp = GetInt16(reader, 7);
                alItems.Add(item);
            }

            reader.Close();
            return alItems;
        }


        //根据tangid获取fiber list 
        public ArrayList get_fiber_list_to_tangid(int tang_id)
        {

            ArrayList alItems = new ArrayList();

            return alItems;

        }


        //根据fiber id来获取所有parentid == fiber id的line列表
        public ArrayList get_line_list_according_to_fiber(int fiberid)
        {
            ArrayList alItems = new ArrayList();

            return alItems;
        }


        //得到纤芯的所有信息
        //返回line_info类型纤芯信息数组,
        //举例：ArrayList al = get_line_info(16);
        public ArrayList get_line_info(int lineid, int tangid)
        {
            ArrayList alItems = new ArrayList();
            string sql = "select * from line_table where scdid=" + scdid + " and idline_table =" + lineid;
            MySqlCommand command = new MySqlCommand(sql, conn);
            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                line_info line = new line_info();

                line.lineid = lineid;
                line.portindex = GetInt16(reader, 10);
                line.sendPort = GetInt16(reader, 1);
                line.recvPort = GetInt16(reader, 2);
                alItems.Add(line);
            }

            reader.Close();


            for (int i = 0; i < alItems.Count; i++)
            {
                line_info item = (line_info)alItems[0];
                string gp_sql = "select row,col from gp_table where scdid=" + scdid + " and idline =" + item.lineid + " and tang =" + tangid;
                MySqlCommand gp_command = new MySqlCommand(gp_sql, conn);
                MySqlDataReader gp_reader = gp_command.ExecuteReader();
                if (gp_reader.Read())
                {
                    item.gp_col = GetInt16(gp_reader, 0);
                    item.gp_row = GetInt16(gp_reader, 1);
                }
                gp_reader.Close();

                ArrayList alSendPort = get_port(new int[] { (int)item.sendPort });
                if (alSendPort.Count > 0)
                {
                    port_item port = (port_item)alSendPort[0];
                    int parentType = (int)port.parentType;
                    if (parentType == 1)
                    {
                        String sql1 = "select ieddesc,f.name from ied_table i,folder_table f where f.idfolder_table=" + port.parentid + " and f.deviceId=i.idied_table";
                        MySqlCommand command1 = new MySqlCommand(sql1, conn);
                        MySqlDataReader reader1 = command1.ExecuteReader();
                        if (reader1.Read())
                        {
                            item.iedname1 = GetString(reader1, 0);
                            item.sendPortName = GetString(reader1, 1) + "/" + port.portDesc;
                        }
                        reader1.Close();
                    }
                    else if (parentType == 0)
                    {
                        item.sendPortName = port.portDesc;
                        ArrayList alIed = get_ied(new int[] { (int)item.sendPort });
                        if (alIed.Count > 0)
                        {
                            item.iedname1 = ((ied_item)alIed[0]).ieddesc;
                        }
                    }
                }
                ArrayList alInfoline = get_inforline_from_port(new int[] { (int)item.sendPort });
                if (alInfoline.Count > 0)
                {
                    item.remark = ((inforline_item)alInfoline[0]).name;
                }

                ArrayList alRecvPort = get_port(new int[] { (int)item.recvPort });
                if (alRecvPort.Count > 0)
                {
                    port_item port = (port_item)alRecvPort[0];
                    int parentType = (int)port.parentType;
                    if (parentType == 1)
                    {
                        String sql2 = "select ieddesc,f.name from ied_table i,folder_table f where f.idfolder_table=" + port.parentid + " and f.deviceId=i.idied_table";
                        MySqlCommand command2 = new MySqlCommand(sql2, conn);
                        MySqlDataReader reader2 = command2.ExecuteReader();
                        if (reader2.Read())
                        {
                            item.iedname2 = GetString(reader2, 0);
                            item.recvPortName = GetString(reader2, 1) + "/" + port.portDesc;
                        }
                        reader2.Close();
                    }
                    else if (parentType == 0)
                    {
                        item.recvPortName = port.portDesc;
                        ArrayList alIed = get_ied(new int[] { (int)item.recvPort });
                        if (alIed.Count > 0)
                        {
                            item.iedname2 = ((ied_item)alIed[0]).ieddesc;
                        }

                    }
                }



            }

            return alItems;
        }



        public void close_connection()
        {
            if (conn.State == ConnectionState.Open)
            {
                Console.WriteLine("关闭数据库连接");
                conn.Close();
            }
        }

    }
}
